# -*- coding:utf-8 -*-
# 接口端，通过http接收传入的数据
import sqlite3
import json
import time
from flask import Flask, request, render_template

app = Flask(__name__)


def open_db():
    conn = sqlite3.connect('toDoList.db')

    print("Opened database successfully")

    # 待办清单
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS TODOLIST
           (ID  INTEGER PRIMARY KEY  NOT NULL,
           TITLE           TEXT      NOT NULL,
           CREATEDATE      CHAR(25)  NOT NULL,
           TODODATE        CHAR(25),
           FINISHDATE      CHAR(25),
           LEVEL           INT       DEFAULT 2,
           STAUTS          INT       DEFAULT 0);''')
    # STAUTS: 0未处理，1正在处理，2被中断，3完成，4作废



    # 工作内容
    c.execute('''CREATE TABLE IF NOT EXISTS WORKLIST
           (ID  INTEGER PRIMARY KEY  NOT NULL,
           TITLE           TEXT    NOT NULL,
           STARTDATE       CHAR(25)  NOT NULL,
           FINISHDATE      CHAR(25),
           TAG             CHAR(250),
           REMARK          TEXT);''')

    # 中断统计
    c.execute('''CREATE TABLE IF NOT EXISTS BREAKLIST
           (ID  INTEGER PRIMARY KEY  NOT NULL,
           TITLE           TEXT    NOT NULL,
           TAG             CHAR(250),
           BREAKDATE       CHAR(25)  NOT NULL);''')

    # 备注表
    c.execute('''CREATE TABLE IF NOT EXISTS MARKLIST
           (ID  INTEGER PRIMARY KEY  NOT NULL,
           TITLE           TEXT    NOT NULL,
           CREATEDATE      CHAR(25)  NOT NULL);''')

    print("Table created successfully")
    conn.commit()
    conn.close()
    return conn


@app.route("/action/", methods=["GET", "POST"])
def action():
    print("In action")
    print('请求方式为------->', request.method)
    # 默认返回内容
    return_dict = {'return_code': '200', 'return_info': '传入成功', 'result': True}
    # 获取传入的参数 get
    # get_data = request.args.to_dict()
    # data_str =str(get_data.get('data'))
    # 获取传入的参数 post
    data_str = json.loads(request.get_data(as_text=True))
    qurey_data = data_str['data']
    act = data_str['act']
    # 判断入参是否为空
    if (qurey_data is None) or (act is None):
        return_dict['return_code'] = '5004'
        return_dict['return_info'] = '必填请求参数不能为空'
        return_dict['result'] = 'False'
        return json.dumps(return_dict, ensure_ascii=False)
    # 不同的行动参数调用不同的函数
    print(act)
    if act == "add" or act == "a":
        # 插入数据失败后返回错误信息
        if not add(qurey_data):
            return_dict['return_code'] = '500'
            return_dict['return_info'] = '写入数据库失败'
            return_dict['result'] = 'False'
        else:
            return_dict['return_info'] = '成功添加待办'
    elif act == "next" or act == "cn" or act == "c" or act == "n":
        data_dict = {}
        qurey_result = next(qurey_data)
        if qurey_result:
            # 把二维元组转换为字典，方便最后组装成json返回
            for i in range(len(qurey_result)):
                data_dict[i] = qurey_result[i][0]
            # 方便客户端取数
            data_dict['cnt'] = len(qurey_result)
        else:
            data_dict['cnt'] = 0
        return_dict['data'] = data_dict
    elif act =="start":
        if not start_mission(qurey_data):
            return_dict['return_code'] = '500'
            return_dict['return_info'] = '更新数据库失败'
            return_dict['result'] = 'False'
        else:
            return_dict['return_info'] = '开始任务成功'
    elif act == "br" or act == "b":
        # 中断当前任务
        if not breake_mission():
            return_dict['return_code'] = '500'
            return_dict['return_info'] = '更新数据库失败'
            return_dict['result'] = 'False'
        else:
            return_dict['return_info'] = '已中断任务'
    elif act =="done" or act == "d":
        # 完成任务
        if not done_mission(qurey_data):
            return_dict['return_code'] = '500'
            return_dict['return_info'] = '更新数据库失败'
            return_dict['result'] = 'False'
        else:
            return_dict['return_info'] = '已完成任务'

    return json.dumps(return_dict, ensure_ascii=True)


# @app.route("/add/", methods=["GET", 'POST'])
def add(qurey_data=""):
    """
    功能：添加待办事项
    入参：待办内容
    """
    print("In add")
    flag = True
    # 对参数进行操作
    try:
        conn = sqlite3.connect('toDoList.db')
        # conn = con
        cor = conn.cursor()
        print("qurey_data:", qurey_data)
        local_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))
        todo_day = time.strftime("%Y-%m-%d", time.localtime(time.time()))
        print("local_time:",local_time)
        cur = cor.execute("SELECT TITLE FROM TODOLIST WHERE (STAUTS = 0 OR STAUTS = 1 OR STAUTS = 2) AND TITLE = ?", (qurey_data,))
        if cur.fetchall():
            print(cur.fetchall())
            flag = False
        else:
            cor.execute("INSERT INTO TODOLIST (ID,TITLE,CREATEDATE,TODODATE,FINISHDATE,LEVEL,STAUTS) \
                         VALUES (?,?,?,?,?,?,?) ", ((None, qurey_data, local_time, todo_day, None, 2, 0)))
        """
        elif act == 'wr':
            cor.execute("INSERT INTO WORKLIST (ID,TITLE,STARTDATE,FINISHDATE,TAG,REMARK) \
                        VALUES (?,?,?,?,?,?) ", ((None, qurey_data, local_time, '', '', '')))
        elif act == 'br':
            cor.execute("INSERT INTO BREAKLIST (ID,TITLE,TAG,BREAKDATE) \
                        VALUES (?,?,?,?) ", ((None, qurey_data, '', '')))
        elif act == 'mar':
            cor.execute("INSERT INTO MARKLIST (ID,TITLE,CREATEDATE) \
                         VALUES (?,?,?) ", ((None, qurey_data, local_time)))
        else:
            pass
        """
        conn.commit()
        conn.close()
        # conn.text_factory = str  # 此处是关键,sqlite默认文本存取是Unicode
    except Exception as bug:
        flag = False
        with open('err.log', 'a') as f:
            local_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
            f.write(str(local_time))
            f.write("In Add")
            f.write(str(bug))
            f.write('\n')
    return flag


def next(qurey_data=""):
    print("In next")
    list_data = []
    # 对参数进行操作
    local_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
    try:
        conn = sqlite3.connect('toDoList.db')
        # conn = con
        cor = conn.cursor()
        print("qurey_data:", qurey_data)
        print("local_time:", local_time)
        # 通过关键字获取下一条任务（如果没有关键字，则获取默认的前X条）
        if qurey_data == "":
            cur = cor.execute("SELECT TITLE FROM TODOLIST \
                               WHERE (STAUTS = 0 OR STAUTS = 2) \
                               ORDER BY CREATEDATE, LEVEL ASC \
                               limit 0,10")
        else:
            cur = cor.execute("SELECT TITLE FROM TODOLIST \
                               WHERE (STAUTS = 0 OR STAUTS = 2) AND TITLE LIKE ? \
                               ORDER BY CREATEDATE, LEVEL ASC \
                               limit 0,10", ('%' + qurey_data + '%',))
        list_data = cur.fetchall()
        print("list_data:", list_data)
        conn.commit()
        conn.close()
    except Exception as bug:
        print(bug)
        with open('err.log', 'a') as f:
            f.write(str(local_time))
            f.write("In Add")
            f.write(str(bug))
            f.write('\n')
    return list_data


def start_mission(title=""):
    # 通过标题开始任务
    # 如果是不存在的标题，则直接插入
    flag = True
    try:
        print("in start_mission")
        print(title)
        local_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        todo_day = time.strftime("%Y-%m-%d", time.localtime(time.time()))
        conn = sqlite3.connect('toDoList.db')
        # conn = con
        cor = conn.cursor()
        # 判断任务是否存在
        cur = cor.execute("SELECT TITLE FROM TODOLIST WHERE (STAUTS = 0 OR STAUTS = 1 OR STAUTS = 2) AND TITLE = ?", (title,))
        if cur.fetchall():
            print("已经有任务")
            # 开始新任务，默认当前的任务进入开始状态
            cor.execute("UPDATE TODOLIST SET STAUTS=3, FINISHDATE=? WHERE STAUTS=1",(local_time,))
            cur = cor.execute("UPDATE TODOLIST SET STAUTS=1 WHERE TITLE=?",((title,)))
            print(cur.fetchall())
        else:
            print("新增一个任务")
            # 如果这个任务还不存在，则新增并开始
            cor.execute("INSERT INTO TODOLIST (ID,TITLE,CREATEDATE,TODODATE,FINISHDATE,LEVEL,STAUTS) \
                         VALUES (?,?,?,?,?,?,?) ", ((None, title, local_time, todo_day, None, 2, 1)))

        conn.commit()
        conn.close()
    except Exception as err:
        print(err)
        flag = False
    return flag


def breake_mission():
    # 中断，修改当前进行中的任务为暂停状态
    flag = True
    try:
        print("in breake_mission")
        # local_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        conn = sqlite3.connect('toDoList.db')
        # conn = con
        cor = conn.cursor()
        # 开始新任务，默认当前的任务进入完成状态
        cur = cor.execute("UPDATE TODOLIST SET STAUTS=2 WHERE STAUTS=1")
        print(cur.fetchall())
        conn.commit()
        conn.close()
    except Exception as err:
        print(err)
        flag = False
    return flag

def done_mission(qurey_data=""):
    # 中断，修改当前进行中的任务为暂停状态
    flag = True
    try:
        print("in done_mission")
        local_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        conn = sqlite3.connect('toDoList.db')
        # conn = con
        cor = conn.cursor()
        # 传入的参数不为空，就默认把标题内容变为完成
        if qurey_data:
            cur = cor.execute("SELECT TITLE FROM TODOLIST WHERE TITLE = ?", (qurey_data,))
            # 如果数据库还没有保存到这个任务，直接新增一个完成状态的任务
            if cur.fetchall():
                cur = cor.execute("UPDATE TODOLIST SET STAUTS=3 ,FINISHDATE = ? WHERE TITLE = ?", (local_time, qurey_data))
            else:
                cur = cor.execute("UPDATE TODOLIST SET STAUTS=3 ,FINISHDATE = ? WHERE TITLE = ?", (local_time, qurey_data))
                cor.execute("INSERT INTO TODOLIST (ID,TITLE,CREATEDATE,TODODATE,FINISHDATE,LEVEL,STAUTS) \
                                         VALUES (?,?,?,?,?,?,?) ",
                            ((None, qurey_data, local_time, local_time, None, 2, 3)))
        else:
            # 开始新任务，默认当前的任务进入完成状态
            print("没有参数，默认完成上一个任务")
            cur = cor.execute("UPDATE TODOLIST SET STAUTS=3 ,FINISHDATE = ? WHERE STAUTS=1", (local_time, ))
        print(cur.fetchall())
        conn.commit()
        conn.close()
    except Exception as err:
        print(err)
        flag = False
    return flag


# 显示待办列表
@app.route("/", methods=["GET"])
def index():
    conn = sqlite3.connect('toDoList.db')
    # conn = con
    cor = conn.cursor()
    cur = cor.execute("SELECT ID, TITLE, CREATEDATE, TODODATE, LEVEL, STAUTS FROM TODOLIST WHERE STAUTS = 0 OR STAUTS = 1 OR STAUTS = 2")
    tmp_data = cur.fetchall()
    print(tmp_data)
    conn.close()
    return render_template('list.html', list_data=tmp_data)

# 显示待办列表（针对树莓派显示调整）
@app.route("/raspi", methods=["GET"])
def index_raspi():
    conn = sqlite3.connect('toDoList.db')
    # conn = con
    cor = conn.cursor()
    cur = cor.execute("SELECT TITLE, CREATEDATE FROM TODOLIST WHERE STAUTS = 0 OR STAUTS = 1 OR STAUTS = 2")
    tmp_data = cur.fetchall()
    # print(tmp_data)
    conn.close()
    return render_template('RasPi_list.html', list_data=tmp_data)

# 更新待办列表
@app.route("/list_update/", methods=["GET"])
def update_list():
    return_dict = {'return_code': '200', 'return_info': '传入成功', 'result': True}
    try:
        get_data = request.args.to_dict()
        id = str(get_data.get('id'))
        # print(id)
        local_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        conn = sqlite3.connect('toDoList.db')
        # conn = con
        cor = conn.cursor()
        cur = cor.execute("UPDATE TODOLIST SET STAUTS=1,FINISHDATE=? WHERE ID=?",((local_time, id)))
        print(cur.fetchall())
        conn.commit()
        conn.close()
    except Exception as err:
        print(err)
        return_dict['return_info']= err
    return json.dumps(return_dict, ensure_ascii=True)


if __name__ == "__main__":
    con = open_db()
    app.run(host = "0.0.0.0",port=8081,debug=False)
